﻿<!DOCTYPE html>
<html>
	<head>
		<title>Sort and filter data</title>
		<meta charset="utf-8" />
		<meta name="description" content="Sort your data in ascending or descending order in a spreadsheet and use filters" />
		<link type="text/css" rel="stylesheet" href="../../../../../../common/main/resources/help/editor.css" />
		<link type = "text/css" rel = "stylesheet" href = "../../images/sprite.css" />
        <script type="text/javascript" src="../callback.js"></script>
        <script type="text/javascript" src="../search/js/page-search.js"></script>
	</head>
	<body>
		<div class="mainpart">
        <div class="search-field">
            <input id="search" class="searchBar" placeholder="Search" type="text" onkeypress="doSearch(event)">
        </div>
			<h1>Sort and filter data</h1>
			<h3>Sort Data</h3>
			<p>You can quickly sort the data in the <a href="https://www.onlyoffice.com/spreadsheet-editor.aspx" target="_blank" onclick="onhyperlinkclick(this)"><b>Spreadsheet Editor</b></a> using one of the following options:</p>
			<ul>
			<li><b>Ascending</b> is used to sort the data in ascending order - from A to Z alphabetically or from the smallest to the largest number for numerical data.</li>
			<li><b>Descending</b> is used to sort the data in descending order - from Z to A alphabetically or from the largest to the smallest for numerical data.</li>
			</ul>
            <p class="note"><b>Note</b>: the <b>Sort</b> options are accessible from both <b>Home</b> and <b>Data</b> tab.</p>
			<p>To sort the data,</p>
			<ol>
				<li>select a cell range you wish to sort (you can select a single cell in a range to sort the entire range),
                </li>
				<li>click the <b>Sort ascending</b> <div class = "icon icon-sortatoz"></div> icon situated on the <b>Home</b> or <b>Data</b> tab of the top toolbar to sort the data in ascending order,
					<br />OR<br />
					click the <b>Sort descending</b> <div class = "icon icon-sortztoa"></div> icon situated on the <b>Home</b> or <b>Data</b> tab of the top toolbar to sort the data in descending order.
				</li>
			</ol>
            <p class="note"><b>Note</b>: if you select a single column/row within a cell range or a part of the column/row, you will be asked if you want to expand the selection to include adjacent cells or sort the selected data only.</p>
			<p>You can also sort your data using the <b>contextual menu</b> options. Right-click the selected range of cells, select the <b>Sort</b> option from the menu and then select <b>Ascending</b> or <b>Descending</b> option from the submenu.</p>
            <p>It's also possible to sort the data by color using the contextual menu:</p>
            <ol>
                <li>right-click a cell containing the color by which you want to sort the data,</li>
                <li>select the <b>Sort</b> option from the menu,</li>
                <li>
                    select the necessary option from the submenu:
                    <ul>
                        <li><b>Selected Cell Color on top</b> - to display the entries with the same cell background color on the top of the column,</li>
                        <li><b>Selected Font Color on top</b> - to display the entries with the same font color on the top of the column.</li>
                    </ul>
                </li>
            </ol>            
                        
			<h3 id="filter">Filter Data</h3>
			<p>To display only the rows that meet certain criteria and hide other ones, make use of the <b>Filter</b> option.</p>
            <p class="note"><b>Note</b>: the <b>Filter</b> options are accessible from both <b>Home</b> and <b>Data</b> tab.</p>
            <b>To enable a filter,</b>
            <ol>
                <li>Select a cell range containing data to filter (you can select a single cell in a range to filter the entire range),</li>
                <li>
                    Click the <b>Filter</b> <div class = "icon icon-sortandfilter"></div> icon situated at the <b>Home</b> or <b>Data</b> tab of the top toolbar.
                    <p>The drop-down arrow <span class = "icon icon-dropdownarrow"></span> will appear in the first cell of each column of the selected cell range. It means that the filter is enabled.</p>
                </li>
            </ol>
            <p><b>To apply a filter,</b></p>
            <ol>
                <li>Click the drop-down arrow <div class = "icon icon-dropdownarrow"></div>. The <b>Filter</b> option list will open: 
                <p><img alt="Filter window" src="../images/filterwindow.png" /></p>
                <p class="note"><b>Note</b>: you can adjust the size of the filter window by dragging its right border to the right or to the left to display the data as convenient as possible.</p>
                </li>
                <li>
                <p>Adjust the filter parameters. You can proceed in one of the following ways: select the data to display, filter the data by certain criteria or filter data by color.</p>
                    <ul>
                        <li>
                            <b>Select the data to display</b>
                            <p>Uncheck the boxes near the data you need to hide. For your convenience, all the data within the <b>Filter</b> option list are sorted in ascending order.</p>
                            <p>The number of unique values in the filtered range is displayed to the right of each value within the filter window.</p>
                            <p class="note"><b>Note</b>: the <b>{Blanks}</b> check box corresponds to the empty cells. It is available if the selected cell range contains at least one empty cell.</p>
                            <p>To facilitate the process, make use of the search field on the top. Enter your query, entirely or partially, in the field <!--and press the Enter key--> - the values that include these characters will be displayed in the list below. The following two options will be also available:</p>
                            <ul>
                                <li><b>Select All Search Results</b> - is checked by default. It allows selecting all the values that correspond to your query in the list.</li>
                                <li><b>Add current selection to filter</b> - if you check this box, the selected values will not be hidden when you apply the filter.</li>
                            </ul>
                            <p>After you select all the necessary data, click the <b>OK</b> button in the <b>Filter</b> option list to apply the filter.</p>
                        </li>
                        <li>
                            <b>Filter data by certain criteria</b>
                            <p>Depending on the data in the selected column, you can choose either the <b>Number filter</b> or the <b>Text filter</b> option on the right side of the <b>Filter</b> options list, and then select one of the options from the submenu:</p>
                            <ul>
                                <li>For the <b>Number filter</b> the following options are available: <em>Equals...</em>, <em>Does not equal...</em>, <em>Greater than...</em>, <em>Greater than or equal to...</em>, <em>Less than...</em>, <em>Less than or equal to...</em>, <em>Between</em>, <em>Top 10</em>, <em>Above Average</em>, <em>Below Average</em>, <em>Custom Filter...</em>.</li>
                                <li>For the <b>Text filter</b> the following options are available: <em>Equals...</em>, <em>Does not equal...</em>, <em>Begins with...</em>, <em>Does not begin with...</em>, <em>Ends with...</em>, <em>Does not end with...</em>, <em>Contains...</em>, <em>Does not contain...</em>, <em>Custom Filter...</em>.</li>
                            </ul>
                            <p>After you select one of the above options (apart from <em>Top 10</em> and <em>Above/Below Average</em>), the <b>Custom Filter</b> window will open. The corresponding criterion will be selected in the upper drop-down list. Enter the necessary value in the field on the right.</p>
                            <p>To add one more criterion, use the <b>And</b> radiobutton if you need the data to satisfy both criteria or click the <b>Or</b> radiobutton if either or both criteria can be satisfied. Then select the second criterion from the lower drop-down list and enter the necessary value on the right.</p>
                            <p>Click <b>OK</b> to apply the filter.</p>
                            <p><img alt="Custom Filter window" src="../images/customfilterwindow.png" /></p>
                            <p>If you choose the <em>Custom Filter...</em> option from the <b>Number/Text filter</b> option list, the first criterion is not selected automatically, you can set it yourself.</p>
                            <p>If you choose the <em>Top 10</em> option from the <b>Number filter</b> option list, a new window will open:</p>
                            <p><img alt="Top 10 AutoFilter window" src="../images/topten.png" /></p>
                            <p>The first drop-down list allows choosing if you wish to display the highest (<b>Top</b>) or the lowest (<b>Bottom</b>) values. The second field allows specifying how many entries from the list or which percent of the overall entries number you want to display (you can enter a number from 1 to 500). The third drop-down list allows setting the units of measure: <b>Item</b> or <b>Percent</b>. Once the necessary parameters are set, click <b>OK</b> to apply the filter.</p>
                            <p>If you choose the <em>Above/Below Average</em> option from the <b>Number filter</b> option list, the filter will be applied right now.</p>
                        </li>
                        <li>
                            <b>Filter data by color</b>
                            <p>If the cell range you want to filter contains some cells you have formatted changing their background or font color (manually or using predefined styles), you can use one of the following options:</p>
                            <ul>
                                <li><b>Filter by cells color</b> - to display only the entries with a certain cell background color and hide other ones,</li>
                                <li><b>Filter by font color</b> - to display only the entries with a certain cell font color and hide other ones.</li>
                            </ul>
                            <p>When you select the necessary option, a palette that contains colors used in the selected cell range will open. Choose one of the colors to apply the filter.</p>
                            <p><img alt="Filter data by color" src="../images/filterbycolor.png" /></p>
                        </li>
                    </ul>
                    <p>The <b>Filter</b> <span class = "icon icon-filterbutton"></span> button will appear in the first cell of the column. It means that the filter is applied. The number of filtered records will be displayed at the status bar (e.g. <em>25 of 80 records filtered</em>).</p>
                    <p class="note"><b>Note</b>: when the filter is applied, the rows that are filtered out cannot be modified when autofilling, formatting, deleting the visible contents. Such actions affect the visible rows only, the rows that are hidden by the filter remain unchanged. When copying and pasting the filtered data, only visible rows can be copied and pasted. This is not equivalent to manually hidden rows which are affected by all similar actions.</p>
                </li>
			</ol>
            <h3>Sort filtered data</h3>
            <p>You can set the <b>sorting order</b> of the data you have enabled or applied filter for. Click the drop-down arrow <span class="icon icon-dropdownarrow"></span> or the <b>Filter</b> <span class="icon icon-filterbutton"></span> button and select one of the options in the <b>Filter</b> option list:</p>
                <ul>
                    <li><b>Sort Lowest to Highest</b> - allows sorting the data in ascending order, displaying the lowest value on the top of the column,</li>
                    <li><b>Sort Highest to Lowest</b> - allows sorting the data in descending order, displaying the highest value on the top of the column,</li>
                    <li><b>Sort by cells color</b> - allows selecting one of the colors and displaying the entries with the same cell background color on the top of the column,</li>
                    <li><b>Sort by font color</b> - allows selecting one of the colors and displaying the entries with the same font color on the top of the column.</li>
                </ul>
                <p>The latter two options can be used if the cell range you want to sort contains some cells you have formatted changing their background or font color (manually or using predefined styles).</p>
            <p>The sorting direction will be indicated by an arrow in the filter buttons.</p>
            <ul>
                <li>if the data is sorted in ascending order, the drop-down arrow in the first cell of the column looks like this: <div class = "icon icon-lowesttohighest"></div> and the <b>Filter</b> button looks the following way: <div class = "icon icon-lowesttohighest1"></div>.</li>
                <li>if the data is sorted in descending order, the drop-down arrow in the first cell of the column looks like this: <div class = "icon icon-highesttolowest"></div> and the <b>Filter</b> button looks the following way: <div class = "icon icon-highesttolowest1"></div>.</li>
            </ul>
            <p>You can also quickly sort the data by color using the contextual menu options:</p>
            <ol>
                <li>right-click a cell containing the color by which you want to sort the data,</li>
                <li>select the <b>Sort</b> option from the menu,</li>
                <li>
                    select the necessary option from the submenu:
                    <ul>
                        <li><b>Selected Cell Color on top</b> - to display the entries with the same cell background color on the top of the column,</li>
                        <li><b>Selected Font Color on top</b> - to display the entries with the same font color on the top of the column.</li>
                    </ul>
                </li>
            </ol>
            <h3>Filter by the selected cell contents</h3>
            <p>You can also quickly filter your data by the selected cell contents using the <b>contextual</b> menu options. Right-click a cell, select the <b>Filter</b> option from the menu and then select one of the available options:</p>
			<ul>
                <li><b>Filter by Selected cell's value</b> - to display only the entries with the same value as the selected cell contains.</li>
                <li><b>Filter by cell's color</b> - to display only the entries with the same cell background color as the selected cell has.</li>
                <li><b>Filter by font color</b> - to display only the entries with the same cell font color as the selected cell has.</li>
            </ul>
            <h3 id="tabletemplate">Format as Table Template</h3>
			<p>To facilitate your work with data, the <b>Spreadsheet Editor</b> allows you to apply a table template to a selected cell range automatically enabling the filter. To do that,</p>
			<ol>
				<li>select a range of cells you need to format,</li>
				<li>click the <b>Format as table template</b> <div class = "icon icon-tabletemplate"></div> icon situated on the <b>Home</b> tab of the top toolbar.</li>
				<li>select the required template in the gallery,</li>
				<li>in the opened pop-up window check the cell range to be formatted as a table,</li>
				<li>check the <b>Title</b> if you wish the table headers to be included in the selected cell range, otherwise the header row will be added at the top while the selected cell range will be moved one row down,</li>
				<li>click the <b>OK</b> button to apply the selected template.</li>
			</ol>
			<p>The template will be applied to the selected range of cells and you will be able to edit the table headers and apply the filter to work with your data. To learn more on working with formatted tables, please refer to <a href="../UsageInstructions/FormattedTables.htm" onclick="onhyperlinkclick(this)">this page</a>.</p>
            <h3>Reapply Filter</h3>
            <p>If the filtered data has been changed, you can refresh the filter to display an up-to-date result:</p>
            <ol>
                <li>click the <b>Filter</b> <div class = "icon icon-filterbutton"></div> button in the first cell of the column that contains the filtered data,</li>
                <li>select the <b>Reapply</b> option in the opened <b>Filter</b> option list.</li>
            </ol>
            <p>You can also right-click a cell within the column that contains the filtered data and select the <b>Reapply</b> option from the contextual menu.</p>
            <h3>Clear Filter</h3>
			<p>To clear the filter,</p>
            <ol>
                <li>click the <b>Filter</b> <div class = "icon icon-filterbutton"></div> button in the first cell of the column that contains the filtered data,</li>
                <li>select the <b>Clear</b> option in the opened <b>Filter</b> option list.</li>
            </ol>
            <p>You can also proceed in the following way:</p>
				<ol>
					<li>select the range of cells containing the filtered data,</li>
					<li>click the <b>Clear filter</b> <div class = "icon icon-clearfilter"></div> icon situated on the <b>Home</b> or <b>Data</b> tab of the top toolbar.</li>
				</ol>
            <p>The filter will remain enabled, but all the applied filter parameters will be removed, and the <b>Filter</b> <span class="icon icon-filterbutton"></span> buttons in the first cells of the columns will change into the drop-down arrows <span class="icon icon-dropdownarrow"></span>.</p>
            <h3>Remove Filter</h3>
            <p>To remove the filter,</p>
            <ol>
                <li>select the range of cells containing the filtered data,</li>
                <li>click the <b>Filter</b> <div class = "icon icon-sortandfilter"></div> icon situated on the <b>Home</b> or <b>Data</b> tab of the top toolbar.</li>
            </ol>
            <p>The filter will be disabled, and the drop-down arrows <span class="icon icon-dropdownarrow"></span> will disappear from the first cells of the columns.</p>
            <h3>Sort data by several columns/rows</h3>
            <p>To sort data by several columns/rows you can create several sorting levels using the <b>Custom Sort</b> function.</p>
            <ol>
                <li>select a cell range you wish to sort (you can select a single cell to sort the entire range),</li>
                <li>click the <b>Custom Sort</b> <span class="icon icon-customsort"></span> icon situated on the <b>Data</b> tab of the top toolbar,</li>
                <li>
                    the <b>Sort</b> window will appear. Sorting by columns is selected by default.
                    <p><img alt="Custom Sort window" src="../images/customsortwindow.png" /></p>
                <li>
                    use the <b>Levels</b> sections to add new levels and to manage the added levels.
                            <ul>
                                <li><b>New</b> button to add a new level, select the second column / row you want to sort and specify other sorting parameters in the <b>Then by</b> field as described above. If necessary, add more levels in the same way,
                                <li><b>Copy</b> button to copy the selected level and to duplicate all the existing settings,</li>
                                <li>
                                    <b>Options</b> button to change the sorting orientation (i.e. sorting data by rows instead of columns). Click the button to open the <b>Sort Options</b> window:
                                    <p><img alt="Sort Options window" src="../images/sortoptionswindow.png" /></p>
                                    <ul>
                                        <li>check the <b>My data has headers</b> box, if necessary</li>
                                        <li>choose the necessary <b>Orientation</b>: <b>Sort top to bottom</b> to sort data by columns or <b>Sort left to right</b> to sort data by rows,</li>
                                        <li>click <b>OK</b> to apply the changes and close the window.</li>
                                    </ul>

                                <li><b>Delete</b> button to delete the selected level,
                                <li>Arrow buttons <b>Move the level up</b> / <b>Move the level down</b> to change the order of levels.</li>
                                </ul>

                                    <li>
                                        set the first sorting level in the <b>Sort by</b> field:
                                        <p><img alt="Custom Sort window" src="../images/customsortwindow2.png" /></p>
                                        <ul>
                                            <li>in the <b>Column</b> / <b>Row</b> section, select the first column / row you want to sort,</li>
                                            <li>in the <b>Sort on</b> list choose one of the following options: <b>Values</b>, <b>Cell color</b>, or <b>Font color</b>,</li>
                                            <li>
                                                in the <b>Order</b> list, specify the necessary sorting order. The available options differ depending on the option chosen in the <b>Sort on</b> list:
                                                <ul>
                                                    <li>if the <b>Values</b> option is selected, choose the <b>Ascending</b> / <b>Descending</b> option if the cell range contains numbers or <b>A to Z</b> / <b>Z to A</b> option if the cell range contains text values,</li>
                                                    <li>if the <b>Cell color</b> option is selected, choose the necessary cell color and select the <b>Top</b> / <b>Below</b> option for columns or <b>Left</b> / <b>Right</b> option for rows,</li>
                                                    <li>if the <b>Font color</b> option is selected, choose the necessary font color and select the <b>Top</b> / <b>Below</b> option for columns or <b>Left</b> / <b>Right</b> option for rows.</li>
                                                </ul>
                                            </li>
                                        </ul>
                                    </li>
                                    <li>click <b>OK</b> to apply the changes and close the window.</li>
            </ol>
            <p>The data will be sorted according to the specified sorting levels.</p>
		</div>
	</body>
</html>